package com.qingcheng.dao;

import com.qingcheng.pojo.order.Statistics;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;

public interface StatisticsMapper extends Mapper<Statistics> {

    @Select("SELECT (SELECT COUNT(DISTINCT username)  FROM tb_order WHERE create_time >= #{date1} \n" +
            "AND create_time < #{date2} GROUP BY username) place_sum,\n" +
            "(SELECT COUNT(*) FROM tb_order WHERE create_time >= #{date1} \n" +
            "AND create_time < #{date2}) order_sum,COUNT(*) single_number,\n" +
            "SUM(o.pay_money) money_amount\n" +
            "FROM `tb_order` o  \n" +
            "RIGHT JOIN `tb_order_item` oi\n" +
            "ON o.id=oi.order_id\n" +
            "WHERE create_time >= #{date1} \n" +
            "AND create_time < #{date2}\n")
    public HashMap<String, Long> findOrder(@Param("date1") String date1, @Param("date2") String date2);

    // 查询交易统计选定时间的订单件数
    @Select("SELECT COUNT(*) valid_sum FROM `tb_order` WHERE pay_status='1' \n" +
            "AND is_delete='0'\n" +
            "AND create_time >= #{date1} \n" +
            "AND create_time < #{date2} ")
    public int Order(@Param("date1") String date1, @Param("date2") String date2);

    // 查询统计时间表
    @Select("SELECT IF(SUM(browse_sum) IS NULL,0, SUM(browse_sum)) browseSum ,\n" +
            "IF(SUM(place_sum) IS NULL,0, SUM(place_sum)) placeSum,\n" +
            "IF(SUM(order_sum) IS NULL,0, SUM(order_sum)) orderSum,\n" +
            "IF(SUM(single_number) IS NULL,0, SUM(single_number)) singleNumber,\n" +
            "IF(SUM(valid_sum) IS NULL,0, SUM(valid_sum)) validSum,\n" +
            "IF(SUM(money_amount) IS NULL,0, SUM(money_amount)) moneyAmount,\n" +
            "IF(SUM(return_money) IS NULL,0, SUM(return_money)) returnMoney,\n" +
            "IF(SUM(pay_personsum) IS NULL,0, SUM(pay_personsum)) payPersonsum,\n" +
            "IF(SUM(pay_ordersum) IS NULL,0, SUM(pay_ordersum)) payOrdersum,\n" +
            "IF(SUM(pay_sum) IS NULL,0, SUM(pay_sum)) paySum,\n" +
            "IF(SUM(pay_money) IS NULL,0, SUM(pay_money)) payMoney,\n" +
            "IF(SUM(unit_price) IS NULL,0, SUM(unit_price)) unitPrice\n" +
            "FROM `tb_statistics` WHERE create_date>= #{date1} \n" +
            "AND create_date<= #{date2} \n")
            public Statistics dealfind(@Param("date1") String date1, @Param("date2") String date2);

    // 查询交易统计选定时间的退款金额
    @Select("SELECT SUM(pay_money) FROM tb_order WHERE pay_status='2' \n" +
            "AND create_time >= #{date1} \n" +
            "AND create_time < #{date2}  ")
    public Float Retrun_Money(@Param("date1") String date1, @Param("date2") String date2);

    // 查询交易统计选定时间的有效订单
    @Select("SELECT COUNT(DISTINCT username) pay_personsum,\n" +
            "COUNT(DISTINCT o.id) pay_ordersum,COUNT(oi.num) pay_sum,\n" +
            "SUM(o.pay_money) pay_money\n" +
            "FROM `tb_order` o  \n" +
            "RIGHT JOIN `tb_order_item` oi\n" +
            "ON o.id=oi.order_id\n" +
            "WHERE create_time >= #{date1} \n" +
            "AND create_time <= #{date2}\n" +
            "AND pay_status='1' \n" +
            "AND is_delete='0'\n")
    public HashMap PayOrder(@Param("date1") String date1, @Param("date2") String date2);

    // 查询会员选定日期的不同金额消费情况
    @Select("SELECT COUNT(*) pay1 ,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=51 AND pay_money<=100) pay2,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=101 AND pay_money<=200) pay3,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=201 AND pay_money<=500) pay4,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=501 AND pay_money<=1000) pay5,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=1001 AND pay_money<=5000) pay6,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=5001 AND pay_money<=10000) pay7,\n" +
            "(SELECT COUNT(*) FROM `tb_order` WHERE pay_money>=10001) pay8 \n" +
            "FROM `tb_order` WHERE pay_money>=0 AND pay_money<=50 AND create_time= #{date1} ")
    public HashMap DifferentMoney(@Param("date1") String date1);

    // 查询选定时间的各种商品销售情况
    @Select("SELECT gt.name AS NAME,SUM(oi.num) num,SUM(o.pay_money) money FROM `tb_order` o INNER JOIN `tb_order_item` oi\n" +
            "INNER JOIN qingcheng_goods.tb_category cate \n" +
            "INNER JOIN qingcheng_goods.tb_template gt\n" +
            "ON o.id=oi.order_id WHERE oi.category_id3=cate.id AND cate.template_id=gt.id\n" +
            "AND create_time>= #{date1} \n" +
            "AND create_time<= #{date2} \n" +
            "AND o.pay_status='1'\n" +
            "AND o.order_status='3'\n" +
            "GROUP BY gt.name")
    public List<HashMap> GoodsFind(@Param("date1") String date1, @Param("date2") String date2);

    @Select("SELECT source_type,SUM(pay_money) money ,COUNT(DISTINCT username) username FROM `tb_order` \n" +
            "WHERE source_type IS NOT NULL\n" +
            "AND order_status='3'\n" +
            "AND create_time>= #{date1} \n" +
            "AND create_time< #{date2} \n" +
            "GROUP BY source_type LIMIT 5")
    public List<HashMap> orderSource(@RequestParam("date1")LocalDate date1,@RequestParam("date1")LocalDate date2);
}